#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pandas as pd
import sys
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import warnings
warnings.filterwarnings('ignore')

def classify_test_cases(excel_path):
    """
    分类处理Excel中的测试用例
    
    参数:
    excel_path: Excel文件路径
    """
    
    try:
        # 读取Excel文件的所有sheet
        xls = pd.ExcelFile(excel_path)
        
        # 检查必要的sheet是否存在
        required_sheets = ['第一次全量', '第二次全量', '第三次全量', '第四次全量', '第五次全量']
        existing_sheets = [sheet for sheet in required_sheets if sheet in xls.sheet_names]
        
        if not existing_sheets:
            print(f"错误: 没有找到任何需要的sheet")
            print(f"文件中现有的sheet: {xls.sheet_names}")
            return
        
        print(f"找到 {len(existing_sheets)} 个需要的sheet: {existing_sheets}")
        
        # 读取所有sheet的数据
        sheet_data = {}
        for sheet_name in existing_sheets:
            df = pd.read_excel(xls, sheet_name=sheet_name)
            sheet_data[sheet_name] = df
        
        # 检查必要的列是否存在
        required_columns = ['Module', 'name', 'result']
        for sheet_name, df in sheet_data.items():
            missing_cols = [col for col in required_columns if col not in df.columns]
            if missing_cols:
                print(f"警告: sheet '{sheet_name}' 中缺少必要的列: {missing_cols}")
                # 尝试查找相似的列名
                similar_cols = {}
                for col in missing_cols:
                    similar = [c for c in df.columns if col.lower() in c.lower() or c.lower() in col.lower()]
                    if similar:
                        similar_cols[col] = similar
                
                if similar_cols:
                    print(f"  可能的相似列名: {similar_cols}")
                return
        
        # 标准化列名（去除可能的空格）
        for df in sheet_data.values():
            df.columns = df.columns.str.strip()
        
        # 确保name列为字符串类型
        for df in sheet_data.values():
            df['name'] = df['name'].astype(str).str.strip()
        
        # 获取所有唯一的测试用例名称
        all_names = set()
        for df in sheet_data.values():
            all_names = all_names.union(set(df['name']))
        
        print(f"总共发现 {len(all_names)} 个唯一的测试用例")
        
        # 创建结果DataFrame
        must_pass_data = []
        possible_failed_data = []
        must_failed_data = []
        
        for name in all_names:
            # 存储每个sheet的结果
            sheet_results = {}
            module = None
            
            # 获取该用例在每个sheet中的结果
            for sheet_name, df in sheet_data.items():
                if name in df['name'].values:
                    result = df[df['name'] == name]['result'].iloc[0]
                    # 获取Module信息（使用第一个找到的Module）
                    if module is None:
                        module = df[df['name'] == name]['Module'].iloc[0]
                else:
                    result = None
                sheet_results[sheet_name] = result
            
            # 准备用例信息
            case_info = {
                'Module': module,
                'name': name,
            }
            
            # 添加每个sheet的结果到case_info
            for sheet_name in existing_sheets:
                result = sheet_results.get(sheet_name)
                case_info[sheet_name] = result if result is not None else '未执行'
            
            # 统计failed次数（只统计有结果的情况）
            results_list = []
            for sheet_name in existing_sheets:
                result = sheet_results.get(sheet_name)
                if result is not None:
                    results_list.append(str(result).lower().strip())
            
            # 统计failed次数
            failed_count = sum(1 for r in results_list if r == 'failed')
            total_results = len(results_list)  # 实际有结果的次数
            
            # 分类
            if failed_count == 0:
                # 没有failed结果
                must_pass_data.append(case_info)
            elif failed_count == total_results:
                # 所有结果都是failed
                must_failed_data.append(case_info)
            else:
                # 部分failed
                possible_failed_data.append(case_info)
        
        # 转换为DataFrame
        must_pass_df = pd.DataFrame(must_pass_data)
        possible_failed_df = pd.DataFrame(possible_failed_data)
        must_failed_df = pd.DataFrame(must_failed_data)
        
        # 保存结果到新的Excel文件
        if excel_path.endswith('.xlsx'):
            output_path = excel_path.replace('.xlsx', '_summary.xlsx')
        elif excel_path.endswith('.xls'):
            output_path = excel_path.replace('.xls', '_summary.xlsx')
        else:
            output_path = excel_path + '_summary.xlsx'
        
        # 使用openpyxl创建writer
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            # 写入原始数据
            for sheet_name in existing_sheets:
                sheet_data[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
            
            # 写入分类结果
            if not must_pass_df.empty:
                must_pass_df.to_excel(writer, sheet_name='mustPass', index=False)
            
            if not possible_failed_df.empty:
                possible_failed_df.to_excel(writer, sheet_name='possibleFailed', index=False)
            
            if not must_failed_df.empty:
                must_failed_df.to_excel(writer, sheet_name='mustFailed', index=False)
        
        print("=" * 60)
        print("处理完成！")
        print(f"结果已保存到: {output_path}")
        print("\n分类统计:")
        print(f"  mustPass (始终通过): {len(must_pass_data)} 个用例")
        print(f"  possibleFailed (可能失败): {len(possible_failed_data)} 个用例")
        print(f"  mustFailed (始终失败): {len(must_failed_data)} 个用例")
        
        # 显示一些统计信息
        total_cases = len(all_names)
        print(f"\n总计: {total_cases} 个用例")
        print(f"  通过率: {len(must_pass_data)/total_cases*100:.1f}%")
        print(f"  可能失败率: {len(possible_failed_data)/total_cases*100:.1f}%")
        print(f"  失败率: {len(must_failed_data)/total_cases*100:.1f}%")
        print("=" * 60)
        
    except Exception as e:
        print(f"处理过程中发生错误: {str(e)}")
        import traceback
        traceback.print_exc()

def main():
    """
    主函数
    """
    if len(sys.argv) < 2:
        print("使用方法: python summary_total.py <excel文件路径>")
        print("示例: python summary_total.py a.xlsx")
        print("       python summary_total.py test_cases.xlsx")
        return
    
    excel_file = sys.argv[1]
    
    try:
        classify_test_cases(excel_file)
    except FileNotFoundError:
        print(f"错误: 文件 '{excel_file}' 不存在")
    except Exception as e:
        print(f"错误: {str(e)}")

if __name__ == "__main__":
    main()